<?php

class msell_out extends CI_Model {
	
    public function __construct()
    {
        parent::__construct();
        $this->load->library('serversidedatatables');
    }
	//ambil nama territory dari id user yang bersangkutan
    public function getClusterName($id = '') {

        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_territory_path(f_get_cluster('$id')) as territory_name, f_get_cluster('$id') as territory_id";
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return array();
        }
    }

  /**
   * Tampilkan user yang memiliki previlege sell_out
   * @param  [type] $cluster_id  [description]
   * @param  [type] $sales_group [description]
   * @return [type]              [description]
   */
  public function get_sales_team( $cluster_id, $sales_group ) {
    $this->db->select('uu.user_id, initcap(uu.user_name) as user_name, uu.user_group_id, gg.user_group_caption');
    $this->db->join('t_mtr_user_group AS gg', 'uu.user_group_id = gg.user_group_id AND uu.istatus = 1 AND gg.having_stock = 1');
    $this->db->where("uu.istatus", 1);
    $this->db->where_in("uu.user_group_id", $sales_group );
    $this->db->where("f_get_cluster(uu.user_id)", $cluster_id);
    $this->db->where("f_get_check_jc(uu.user_id,uu.user_group_id)", 1);
    $this->db->order_by("uu.user_group_id, user_name", "asc");
    $query = $this->db->get("t_mtr_user AS uu");
    return $query->result();
  }

	//get nik name from user
    function getNikName($userID) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_nik_name('$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_nik_name'];
        } else {
            return array();
        }
    }
    

    public function get_outlet_list($user_group_id, $user_id) {
      $this->db->select('f_get_channel_id_by_territory_id(jc.territory_id) as channel_id');
      $this->db->select('f_get_channel_name(f_get_channel_id_by_territory_id(jc.territory_id)) as channel_name');
      $this->db->join('t_mtr_territory tt', 'tt.territory_id = jc.territory_id');
      $this->db->where('jc.user_id', $user_id);
      $this->db->where('f_get_is_sgo(f_get_channel_id_by_territory_id(jc.territory_id))', 1);
      $this->db->where('jc.yyyymm', date('Ym'));
      $this->db->where('jc.d'.date('j'), 1);
      $query = $this->db->get('t_jc_'.$user_group_id.' jc');
      return $query->result();
    }
    
    //ambil data termin untuk jenis pembayaran
    public function termins() {
        $query =$this->db->get_where("t_mtr_top",array("istatus" => 1));
        return $query->result();
    }
    
    function getItems($channel_id) {
        $where = '(SELECT COUNT(x.iccid) from t_trx_sellout_detail x where x.iccid = d.iccid) = 0';
        $this->db->select("d.iccid, itm.item_name, itm.item_code, grp.item_group_name, i.mdn");
        $this->db->from("t_trx_sell_in_detail as d");
        $this->db->join("t_trx_sell_in as h", "d.sell_in_id = h.sell_in_id");
        $this->db->join("t_mtr_item as i", "d.iccid = i.iccid");
        $this->db->join("t_mtr_item_mapping as itm", "i.item_mapping_id = itm.item_mapping_id");
        $this->db->join("t_mtr_item_group as grp", "itm.item_group_id = grp.item_group_id");
        $this->db->where("h.channel_id", $channel_id);
        $this->db->where('d.istatus <', 4);
        $this->db->where($where);
        $this->db->order_by("h.sell_in_date", "asc");
        $query = $this->db->get();


        return $query->result();
    }
    
     //lakukan insert ke table t_trx_sell_in
    function savesellout() {

        $dblokal   = $this->load->database("default", true);
        $sell_date = $this->input->post("sell_date");
        $remark    = $this->input->post("remark");
        $kon_date  = $this->input->post("kon_date");
        $top_id    = $this->input->post("term_of_payment");
        
        /* yaddisurahman@gmail.com -- 19 Mei
          sales_id 	=> 92000418 | Apriana
          channel_id 	=> JBL20689 | ABOET CELL
          need to be explode()
         */
        
        $sid = $this->input->post("sales_person");
        $sales_id = explode(" ", $sid);
        $cid = $this->input->post("outlet_name");
        $channel_id = explode(" ", $cid);
        $query=$this->db->query("select f_get_territory_id_by_channel_id('" . $channel_id[0] . "') as id")->row();
        //print_r($query->id);echo "select f_get_territory_id_by_channel_id('" . $channel_id[0] . "') as id";return;
        $data = array(

            "created_on"   => $sell_date,
            "updated_on"   => $sell_date,
            "created_by"   => $this->session->userdata('username'),
            "updated_by"   => $this->session->userdata('username'),
            "sales_id"     => $sales_id[0],
            "territory_id" => $query->id,//;//$channel_id[0],
            "sellout_date" => $kon_date,
            "access_via"   => "web",
            //"istatus"    => 1,
            "remark"       =>$remark
        );
        // print_r($data);die();
        
        $dblokal->insert("t_trx_sellout", $data);
        $last = $this->db->insert_id();

        // 20120514 anggwa@gmail.com -> log
        $this->mglobal->saveLog('sell-out', 'create', $last);

        return $last;
    }

     //lakukan insert ke table t_trx_sell_in_detail
    function saveselloutInDetail($sid, $iccid) {
	   if($this->db->query("select count(iccid) as jumlah from t_trx_sellout_detail where iccid='".$ICCID."'")->row()->jumlah==0)
       {       
            $dblokal = $this->load->database("default", true);
            // $iccid = $this->input->post("iccid");	// yaddisurahman
            $sell_date = $this->input->post("sell_date");
            //$query=$this->db->query("select f_get_price('".$iccid."','". $this->session->userdata('username') ."') as id")->row();
            //print_r("select f_get_price('".$sid."','". $this->session->userdata('username') ."') as id");
    
            $data = array(
                // "iccid" => $iccid[$i], 
                "iccid" => $iccid,
                "sellout_id" => $sid,
                "created_by" => $this->session->userdata('username'),
                "created_on" => $sell_date,
                "updated_by" => $this->session->userdata('username'),
                "updated_on" => $sell_date,
    
            );
    
            $dblokal->insert("t_trx_sellout_detail", $data);
    
            // 20120514 anggwa@gmail.com -> log
            $last = $this->db->insert_id();
            //$this->mglobal->saveLog('sell-in-detail', 'create', '(header=' . $sid . ' detail=' . $last . ' iccid=' . $iccid . ')');
    
            //update istatus pada table t_trx_scan_in_detail menjadi 2
            $data2 = array(
                'istatus' => 4,
            );
            $this->db->update('t_trx_sell_in_detail', $data2, array('iccid' => $iccid));
        }
    }

    public function save_sell_out($sales_id, $channel_id, $sell_date, $remark)
    {
      $query=$this->db->query("select f_get_territory_id_by_channel_id('$channel_id') as territory_id")->row();
      // echo $this->db->last_query();
      // die();

      $data = array(
        "created_on"   => date('Y-m-d H:i:s'),
        "created_by"   => $this->session->userdata('username'),
        "sales_id"     => $sales_id,
        "territory_id" => $query->territory_id,
        "sellout_date" => $sell_date,
        "remark"       => $remark,
        "access_via"   => 'web'
      );

      $this->db->insert('t_trx_sellout', $data);
      $trans_id =  $this->db->insert_id();
      $this->mglobal->saveLog('sell-out', 'create', $trans_id);
      return $trans_id;
    }

    public function save_sell_out_detail($sell_out_id, $item) {
      foreach ($item as $iccid) {
        $data[]= array( 
          'iccid'      => $iccid, 
          'sellout_id' => $sell_out_id, 
          'created_on' => date('Y-m-d H:i:s'),
          'created_by' => $this->session->userdata('username'),
        );
      }
      $this->db->insert_batch('t_trx_sellout_detail', $data);
      $this->db->where_in('iccid', $item);
      $this->db->update('t_trx_sell_in_detail', array('istatus' => 4));
      $this->mglobal->saveLog('sell-out-detail', 'detail, iccid='.json_encode($item), $sell_out_id );
    }

    public function getAllSellin() {

        $res = array();
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        /** @edited Alfin Akhret <alfin.akhret@gmail.com>
            /* optimasi query
            */
            $cluster = $this->db->query("select * from f_get_cluster('".$userlogin."')");
            $cluster = $cluster->result();
            $cluster = $cluster[0]->f_get_cluster;
            // echo $cluster;die();

        //mod by anggwa@gmail.com 20120505
//        $sql = "SELECT to_char(created_on, 'DD Mon YYYY') as created_on, sell_in_id, user_id FROM t_trx_sell_in
//				WHERE DATE_PART('day', now() - created_on::timestamp) 
//				BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month'))
//				AND f_get_cluster(user_id) = f_get_cluster('$userlogin')
//				ORDER BY sell_in_id DESC, created_on desc";

        /*
        $query = $dbconn->query("SELECT to_char(created_on, 'DD Mon YYYY') as created_on, sellout_id, sales_id FROM t_trx_sellout
				WHERE DATE_PART('day', now() - created_on::timestamp) 
				BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month'))
				AND f_get_cluster(sales_id) = '$cluster'
				ORDER BY sellout_id DESC, created_on desc");
        */

        /*SSP
        /* edited by alfin akhret, 09/28/2012
        */
        
        $sIndexColumn = "sellout_id"; 
        $status="";
        $defaultOrder = "0";
        $orderingSequence = "desc";
        $flag = '';

        $aColumns = array("created_on", "sellout_id", "user_name");

        $inputQuery = "
                SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
                FROM v_ssp_sellout_2 WHERE DATE_PART('day', now() - created_on::timestamp) BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month')) AND cluster_id = $cluster";

        return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn);


        // if ($query->num_rows() > 0) {
        //     foreach ($query->result() as $res) {
        //         $res->getNikName = $this->getNikName($res->sales_id);
        //         //mod by anggwa@gmail.com 20120505
        //         //$res->tanggalJual = $this->getNikName($res->user_id);
        //     }
        //     return $query->result();
        // } else {
        //     return array();
        // }
    }
        public function getById($id) {
        $id = intval($id);

        // $query = $this->db->where('sell_in_id', $id)->limit(1)->get('t_trx_sell_in');
        $query = $this->db->get_where('t_trx_sellout', array('sellout_id' => $id)); 
        // echo $id; die();

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return array();
        }
    }
    
        public function getPartner($channelId) {
        //Di Blok dulu biar ga error
        $dbconn = $this->load->database("default", TRUE);
        $query = $dbconn->query("SELECT f_get_channel_name(f_get_channel_id_by_territory_id($channelId))");
        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_channel_name'];
            //return $query->result();
        } else {
            return array();
        }
    }
    
    public function getItemDetail($id) {
        $listItem = array();
        // ******** Mod by anggwa@gmail.com 20120504 ******** //
        // $this->db->select('item_name, item_code, item_group_id');
        // $this->db->where('iccid', $id);
        // $q = $this->db->get('t_mtr_item');
        // $listItem = $q->result_array();	
        // $this->db->select('item_group_name');
        // $this->db->where('item_group_id', $listItem[0]['item_group_id']);
        // $q = $this->db->get('t_mtr_item_group');
        // $q = $q->result_array();
        // $listItem[0]['item_group_name'] = $q[0]['item_group_name'];

        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        $query = $dbconn->query("SELECT itm.item_name, itm.mdn,  itm.item_code, grp.item_group_id, grp.item_group_name " .
                "FROM t_mtr_item itm " .
                "JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code " .
                "JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id " .
                "WHERE itm.iccid = '$id' LIMIT 1");
        //$query = $dbconn->query($sql);		
        if ($query->num_rows() > 0) {
            $listItem = $query->result_array();
        } else {
            return array();
        }
        return $listItem;
    }
        function getPrice($iccid = "", $userID = "") {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT * from f_get_price('$iccid','$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_price'];
        } else {
            return array();
        }
    }

    public function validate_item($iccid)
    {
        $this->db->select('iccid');
        $this->db->where("t_trx_sellout_detail.iccid", $iccid );
        $query = $this->db->get("t_trx_sellout_detail");
        if ( $query->num_rows() == 0 ) 
            { return $iccid; }
        else
            { return null; }
    }
}
?>